﻿USE [LibraryDataBase_wl]
GO
/****** 对象:  StoredProcedure [dbo].[GetAllRoomType]    脚本日期: 09/13/2010 14:57:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------------------------------
/*查询所有图书类型(查询图书类型用到,带搜索)*/
create proc [dbo].[GetAllBooKType](@message varchar(50))
as
	if (@message = '')
	begin
		select * from dbo.TabBookType
	end
	else
	begin
		select * from dbo.TabBookType where (TypeName like @message+'%' or Remark like @message+'%')
	end
USE [LibraryDataBase_wl]
GO
/****** 对象:  StoredProcedure [dbo].[GetOpenBookInfoAll]    脚本日期: 09/13/2010 15:08:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetOpenBookInfoAll]
(
  @message varchar(50)
)
AS
SET NOCOUNT ON
IF(@message = '')
begin
  select a.BorrowBookID ,b.BookID,b.BookName,b.BookNumber,c.TypeName,d.StateName,b.BookAuther,b.BookPublicTime,b.BookPrice,b.IsDisk,b.Remark
  from TabBorrowBooks as a ,TabBook as b,TabBookType as c,TabBookState as d
  where  a.BookID = b.BookID and b.TypeID = c.TypeID and b.StateID = d.StateID
end
else
begin
 select a.BorrowBookID ,b.BookID,b.BookName,b.BookNumber,c.TypeName,d.StateName,b.BookAuther,b.BookPublicTime,b.BookPrice,b.IsDisk,b.Remark
  from TabBorrowBooks as a ,TabBook as b,TabBookType as c,TabBookState as d
  where  a.BookID = b.BookID and b.TypeID = c.TypeID and b.StateID = d.StateID and
  (
    b.BookID like @message + '%' or b.BookName like @message + '%' or  b.BookNumber like @message + '%' or c.TypeName like @message + '%'
    or d.StateName like @message + '%' or b.BookAuther like @message + '%' or b.BookPublicTime like @message + '%' or b.BookPrice like  @message + '%'
    or b.IsDisk like @message + '%' or b.Remark like @message + '%'
  )
end
SET NOCOUNT OFF
USE [LibraryDataBase_wl]
GO
/****** 对象:  StoredProcedure [dbo].[OpenBook]    脚本日期: 09/13/2010 15:09:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[OpenBook]
(
   @BookID INT,
   @UserID INT,
   @StartTime DateTime,
   @EndTime DateTime,
   @Remark NVARCHAR
)
AS
INSERT INTO TabBorrowBooks VALUES(@BookID,@UserID,@StartTime,@EndTime,@Remark)
USE [LibraryDataBase_wl]
GO
/****** 对象:  StoredProcedure [dbo].[GetLogin]    脚本日期: 09/13/2010 15:09:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*用户登录*/
create proc [dbo].[GetLogin](@LoginName varchar(50),@LoginPass varchar(50))
as
	select UserID,UserName,UserPass ,Remark  from TabUser
		where UserName = @LoginName and UserPass = @LoginPass
USE [LibraryDataBase_wl]
GO
/****** 对象:  StoredProcedure [dbo].[CloseBook]    脚本日期: 09/13/2010 15:10:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CloseBook]
(
  @BookNumber NVARCHAR
)
AS
SET NOCOUNT ON
DELETE TabBorrowBooks WHERE BookID = (SELECT BookID FROM TabBook WHERE BookNumber = @BookNumber)
SET NOCOUNT OFF

USE [LibraryDataBase_wl]
GO
/****** 对象:  StoredProcedure [dbo].[GetAllRoomType]    脚本日期: 09/13/2010 14:57:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------------------------------
/*根据类型Id查询所有客房信息(状态为空闲)*/
create proc [dbo].[GetBookByTypeId](@TypeId int)
as
	select BookID,BookNumber from dbo.TabBook where StateID = 1 and TypeID=@TypeId